﻿using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using Arche.Data;
using Arche.EnterpriseServices;
using Bowling.Game;

namespace Bowling
{
    public class BowlingDac : DacBase
    {
        public BowlingDac()
        {
        }

        /// <summary>
        /// 유저가 새로 입장하였을때 DB 기록 테이블에 새로운 Row를 추가합니다.
        /// </summary>
        /// <param name="gameInfo"></param>
        /// <returns>입력되어진 기록 일련번호 SEQ를 리턴합니다.</returns>
        public int InsertBolwingNewGame(GameInfo gameInfo)
        {
            return (int) new DacHelper(_dbHelper, "connectDB").SelectScalar(CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_InsertBowlingNewGame",
                SqlParameterHelper.CreateParameter("@USERID", gameInfo.userInfo.userID, SqlDbType.VarChar, 20),
                SqlParameterHelper.CreateParameter("@PLAYDATE", gameInfo.playDate, SqlDbType.DateTime, 8));
            
        }

        /// <summary>
        /// 현재 진행중인 게임의 DB에 스코어와 핀 개수를 업데이트합니다.
        /// </summary>
        /// <param name="gameInfo"></param>
        public void UpdateBowlingGameScore(GameInfo gameInfo, GamePlay gamePlay)
        {
            new DacHelper(_dbHelper, "connectDB").Execute(CommandType.StoredProcedure,
                "dbo.UPAR_Bowling_Bowling_UpdateBowlingGameScore",
                SqlParameterHelper.CreateParameter("@SEQ", gameInfo.recordSeq, SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@ENDDATE", gameInfo.endDate, SqlDbType.DateTime, 8),
                SqlParameterHelper.CreateParameter("@SCORE1", gameInfo.score[0], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE2", gameInfo.score[1], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE3", gameInfo.score[2], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE4", gameInfo.score[3], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE5", gameInfo.score[4], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE6", gameInfo.score[5], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE7", gameInfo.score[6], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE8", gameInfo.score[7], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE9", gameInfo.score[8], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@SCORE10", gameInfo.score[9], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW11", gameInfo.throws[0], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW12", gameInfo.throws[1], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW21", gameInfo.throws[2], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW22", gameInfo.throws[3], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW31", gameInfo.throws[4], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW32", gameInfo.throws[5], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW41", gameInfo.throws[6], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW42", gameInfo.throws[7], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW51", gameInfo.throws[8], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW52", gameInfo.throws[9], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW61", gameInfo.throws[10], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW62", gameInfo.throws[11], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW71", gameInfo.throws[12], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW72", gameInfo.throws[13], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW81", gameInfo.throws[14], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW82", gameInfo.throws[15], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW91", gameInfo.throws[16], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW92", gameInfo.throws[17], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW101", gameInfo.throws[18], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW102", gameInfo.throws[19], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@THROW103", gameInfo.throws[20], SqlDbType.Int, 4),
                SqlParameterHelper.CreateParameter("@LAST_FRAME_NO", gamePlay.frameNo, SqlDbType.Int, 4)
                );
        }

        /// <summary>
        /// 기록보기 테이블에서 모든 기록을 출력하는 함수입니다.
        /// </summary>
        /// <returns></returns>
        public BowlingHyukwon_RecordT[] SelectBowlingGameScoreRecord()
        {

            return (BowlingHyukwon_RecordT[])new DacHelper(_dbHelper, "connectDB").SelectMultipleEntities(
                typeof(BowlingHyukwon_RecordT),
                BowlingMappingCache.GetDataMappings("BowlingHyukwon_RecordT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingGameScoreRecordAll"
                );
        }

        /// <summary>
        /// 기록보기 테이블에서 로그인 상태일때 접속 아이디의 기록을 선택하여 출력하는 함수입니다.
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public BowlingHyukwon_RecordT[] SelectBowlingGameScoreRecord(string userID)
        {
            
            return (BowlingHyukwon_RecordT[])new DacHelper(_dbHelper, "connectDB").SelectMultipleEntities(
                typeof(BowlingHyukwon_RecordT),
                BowlingMappingCache.GetDataMappings("BowlingHyukwon_RecordT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingGameScoreRecord",
                SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar)
                );
        }


        /// <summary>
        /// 입력된 SEQ 기록 일련번호의 DB 데이터 Row를 삭제합니다.
        /// </summary>
        /// <param name="seq"></param>
        public void DeleteBowlingGameScoreRecord(int seq)
        {
            new DacHelper(_dbHelper, "connectDB").Execute(CommandType.StoredProcedure,
                "dbo.UPAR_Bowling_Bowling_DeleteBowlingGameScoreRecord", SqlParameterHelper.CreateParameter("@SEQ", seq));
        }
        
        /// <summary>
        /// 로그인 시에 유저 아이디와 패스워드로 인증절차를 걸칩니다.
        /// 아이디가 존재하고 패스워드가 일치하면 true를 리턴합니다.
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="userPW"></param>
        /// <returns></returns>
        public bool SelectBowlingGameUserLogin(String userID, String userPW)
        {
            try
            {
                int seq = (int) new DacHelper(_dbHelper, "connectDB").SelectScalar(CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingGameUserLogin",
                SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar, 20),
                SqlParameterHelper.CreateParameter("@PSWORD", userPW, SqlDbType.VarChar, 10));
                return true;
            }
            catch
            {
                return false;
            }
             
        }

        /// <summary>
        /// 새로운 유저를 DB의 유저 테이블에 등록합니다.
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="userPW"></param>
        /// <param name="userName"></param>
        /// <param name="avg"></param>
        public void InsertBolwingNewUser(String userID, String userPW, String userName, int avg)
        {
            new DacHelper(_dbHelper, "connectDB").Execute(CommandType.StoredProcedure,
                "dbo.UPAR_Bowling_Bowling_InsertBowlingNewUser",
                SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar, 20),
                SqlParameterHelper.CreateParameter("@USERNAME", userName, SqlDbType.VarChar, 20),
                SqlParameterHelper.CreateParameter("@PSWORD", userPW, SqlDbType.VarChar, 10),
                SqlParameterHelper.CreateParameter("@AVERAGE", avg, SqlDbType.Int, 4)
                );
        }

        /// <summary>
        /// 유저 등록시에 아이디가 중복되는지 여부를 확인합니다.
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public bool SelectBowlingGameIdCheck(String userID)
        {
            try
            {
                String isID = (String) new DacHelper(_dbHelper, "connectDB").SelectScalar(CommandType.StoredProcedure,
                    "dbo.UPAR_Bowling_Bowling_SelectBowlingIdCheck",
                    SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar, 20)
                    );

                if (isID != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            
        }
        
        /// <summary>
        /// 입장한 유저의 정보를 DB유저 테이블에서 불러옵니다.
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public BowlingHyukwon_UserT SelectBowlingGameUserInfo(String userID)
        {
            BowlingHyukwon_UserT bhu = (BowlingHyukwon_UserT) new DacHelper(_dbHelper, "connectDB").SelectSingleEntity(
                typeof(BowlingHyukwon_UserT),
                BowlingMappingCache.GetDataMappings("BowlingHyukwon_UserT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingGameUserInfo",
                SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar)
                );

            return bhu;
        }

        /// <summary>
        /// 지난 기록 로드 데이터
        /// </summary>
        /// <param name="seqLastRecord"></param>
        /// <returns></returns>
        public BowlingHyukwon_RecordT SelectBowlingGameLastRecord(int seqLastRecord)
        {
            BowlingHyukwon_RecordT bhr = (BowlingHyukwon_RecordT) new DacHelper(_dbHelper, "connectDB").SelectSingleEntity(
                typeof(BowlingHyukwon_RecordT),
                BowlingMappingCache.GetDataMappings("BowlingHyukwon_RecordT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingGameLastRecord",
                SqlParameterHelper.CreateParameter("@SEQ", seqLastRecord, SqlDbType.Int)
                );
            return bhr;
        }

        /// <summary>
        /// 지난 게임 기록중 마지막 기록 일련번호를 리턴
        /// </summary>
        /// <param name="userID">userID</param>
        /// <returns></returns>
        public int SelectBowlingGameLastRecord(String userID)
        {
            //DB에 userID의 마지막 기록이 없다면 -1을 리턴
            int seqLastRecord = -1;

            //DB에서 null 값 생성시 int형 캐스트 오류 때문에 트라이 캐치 사용
            try
            {
                seqLastRecord = (int)new DacHelper(_dbHelper, "connectDB").SelectScalar(CommandType.StoredProcedure,
                    "dbo.UPAR_Bowling_Bowling_SelectBowlingIdCheckLastRecord",
                    SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar, 20)
                    );
            }
            catch (Exception e)
            {

            }

            return seqLastRecord;
        }

        /// <summary>
        /// 지난 게임 기록중 선택한 기록 일련 번호 리턴
        /// </summary>
        /// <param name="userID">userID</param>
        /// <param name="seqLoad">선택한 기록 일련번호</param>
        /// <returns></returns>
        public int SelectBowlingGameLastRecord(String userID, int seqLoad)
        {
            //DB에 userID의 마지막 기록이 없다면 -1을 리턴
            int seqLastRecord = -1;

            try
            {
                seqLastRecord = (int)new DacHelper(_dbHelper, "connectDB").SelectScalar(CommandType.StoredProcedure,
                    "dbo.UPAR_Bowling_Bowling_SelectBowlingIdCheckRecord",
                    SqlParameterHelper.CreateParameter("@USERID", userID, SqlDbType.VarChar, 20),
                    SqlParameterHelper.CreateParameter("@SeqLoad", seqLoad, SqlDbType.Int)
                    );
            }
            catch(Exception e)
            {

            }

            return seqLastRecord;
        }

        /// <summary>
        /// 새로 추가한 테이블의 1번~9번까지의 점수 기록 Select
        /// </summary>
        /// <returns></returns>
        public BowlingHyukwon_SetScoreT[] SelectBowlingGameScore_1_9()
        {
            BowlingHyukwon_SetScoreT[] bhssArr = (BowlingHyukwon_SetScoreT[])new DacHelper(_dbHelper, "connectDB").SelectMultipleEntities(
                typeof(BowlingHyukwon_SetScoreT), BowlingMappingCache.GetDataMappings("BowlingHyukwon_SetScoreT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingScore_1_9", SqlParameterHelper.CreateParameter("@FRAME_NO", 1, SqlDbType.Int)
                );

            return bhssArr;
        }

        /// <summary>
        /// 새로 추가한 테이블의 10번 테이블 점수 기록 select
        /// </summary>
        /// <returns></returns>
        public BowlingHyukwon_SetScoreT SelectBowlingGameScore_10()
        {
            BowlingHyukwon_SetScoreT bhss = (BowlingHyukwon_SetScoreT)new DacHelper(_dbHelper, "connectDB").SelectSingleEntity(
                typeof(BowlingHyukwon_SetScoreT), BowlingMappingCache.GetDataMappings("BowlingHyukwon_SetScoreT"),
                CommandType.StoredProcedure, "dbo.UPAR_Bowling_Bowling_SelectBowlingScore_10", SqlParameterHelper.CreateParameter("@FRAME_NO", 1, SqlDbType.Int)
                );

            return bhss;
        }

        
        /// <summary>
        /// 새로운 점수 테이블 Truncate(Refresh)
        /// </summary>
        public void DeleteBowlingFrameScore()
        {
            new DacHelper(_dbHelper, "connectDB").Execute(CommandType.StoredProcedure,
                "dbo.UPAR_Bowling_Bowling_DeleteBowlingFrameScore");
        }
    }
}
